﻿using System;
using System.Collections.Generic;

using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;

namespace SRM._admin
{
    public partial class DataSync : System.Web.UI.Page
    {
        string[] tableList = { "SRM_AdminUsers_Tbl", "SRM_Applicant_Tbl", "SRM_ApplicantAwards_Tbl", "SRM_ApplicantEducation_Tbl", "SRM_ApplicantEmpHistory_Tbl", "SRM_ApplicantFamilyMembers_Tbl", "SRM_ApplicantFriends_Tbl", "SRM_ApplicantHistory_Tbl", "SRM_ApplicantLanguage_Tbl", "SRM_ApplicantProfMembership_Tbl", "SRM_Audit", "SRM_Code", "SRM_CodeType", "SRM_Log", "SRM_MapIdentityType", "SRM_MapLanguage", "SRM_MapLanguageAbility", "SRM_MapLanguageProficiency", "SRM_MapMaritalStatus", "SRM_MapRace", "SRM_MapRelationship", "SRM_Requisition_Tbl", "SRM_RequisitionApplicant_Tbl", "SRMx_EmailMessage_Tbl", "SRMx_Export_Tbl", "tblDataB2E_SSO","tblDataPersonnelB2E","tblDataPersonnel","tblDataActivation"};

        // Syncing "SRM_Resume_Tbl" is too huge, 
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        private void SyncTable(string tableName)
        {
            System.Data.DataSet ds = new System.Data.DataSet("dsCurrentClaims");
            System.Data.DataTable dtB2E = null;

            using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("Data Source=kldb;Initial Catalog=B2EPilot;User ID=b2epilotwebacct;Password=p@55w0rd;Application Name=e-Overtime;Workstation ID=AN2594"))
            {
                System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = @"SELECT * FROM " + tableName;

                System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
                da.Fill(ds, "B2E");

                if ((ds != null) && (ds.Tables.Count > 0) && (ds.Tables["B2E"].Rows.Count > 0))
                {
                    dtB2E = ds.Tables["B2E"];
                }
            }

            if (dtB2E != null)
            {
                using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SRM"].ConnectionString))
                {
                    System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();

                    conn.Open();
                    using (System.Data.SqlClient.SqlTransaction sqlTran = conn.BeginTransaction())
                    {
                        cmd.CommandText = "DELETE FROM " + tableName;
                        cmd.Transaction = sqlTran;
                        cmd.ExecuteNonQuery();

                        using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(conn, System.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity, sqlTran))
                        {
                            bcp.DestinationTableName = tableName;
                            bcp.WriteToServer(dtB2E);
                        }

                        sqlTran.Commit();
                    }
                }
            }
        }

        protected void btnDataSync_Click(object sender, EventArgs e)
        {
            foreach (string tableName in tableList)
            {
                LiteralMessage.Text = LiteralMessage.Text + string.Format("<p>Start syncing {0}</p>", tableName);
                SyncTable(tableName);
                LiteralMessage.Text = LiteralMessage.Text + string.Format("<p>{0} sync complete</p>", tableName);
            }
        }

    }
}